package dao.mysql;


import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

import model.UserDTO;
import exception.DAOSystemException;
import dao.UserDAO;

public class UserDAOImpl implements UserDAO{
   
    MySqlImpl mysqlImpl = new MySqlImpl();
   
    public void deleteUser(UserDTO user) throws DAOSystemException {
        String deleteSql = "DELETE FROM user WHERE id='"
                + user.getId().trim() + "';";
       
        try {
            mysqlImpl.execute(deleteSql);
        } catch (SQLException e) {
            String error = "Error deleting users. Nested Exception is: " + e;
            Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
            try {
                mysqlImpl.rollBack();
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
            throw new DAOSystemException(error);
        } finally {
            try {
                mysqlImpl.close();
            } catch (Exception e) {
            }
        }
    }
   
    public UserDTO findUser(String userId) throws DAOSystemException{
        UserDTO oneUser = null;
    	if(userId!=null)
    	{
    		 String selectSql = "SELECT * FROM user WHERE id='" + userId.trim()
    	        + "' or userName='" + userId.trim()+ "' or name='" + userId.trim()+"';";

        try{
            ResultSet rs = mysqlImpl.get(selectSql);
            String positionId = "";
           
            String managerId = "";
            if (rs.next()){
           
                oneUser = new UserDTO();
                oneUser.setId(rs.getString("id"));
                oneUser.setName(rs.getString("name"));
                oneUser.setUserName(rs.getString("userName"));
                oneUser.setPassword(rs.getString("password"));
                oneUser.setAddress(rs.getString("address"));
                oneUser.setEmail(rs.getString("email"));
                oneUser.setPhoneNumber(rs.getInt("phoneNumber"));
                positionId = rs.getString("positionId");
               
                managerId = rs.getString("mgrId");
                oneUser.setPosition(new PositionDAOImpl().findPosition(positionId));
               
                if (!managerId.equalsIgnoreCase(oneUser.getId()))
                oneUser.setManager(new UserDAOImpl().findUser(managerId));
                oneUser.setConsumeAnnualAllowance(rs.getDouble("consumeAnnualAllowance"));
                oneUser.setConsumeMedicalAllowance(rs.getDouble("consumeMedicalAllowance"));
               
            }
           
        } catch (Exception e) {
            String error = "Error selecting users. Nested Exception is: " + e;
            Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
            throw new DAOSystemException(error);
        } finally {
            try {
                mysqlImpl.close();
            } catch (Exception e) {
            }
        }
    	}
    	
        return oneUser;
               
   
    }
    public ArrayList<UserDTO> findAllUser() throws DAOSystemException {
        ArrayList<UserDTO> items = new ArrayList<UserDTO>();
        String selectSql = "SELECT * FROM user;";
       
        try {

           
            Logger.getLogger(getClass().getName()).log(Level.INFO,
                    "Executing select: " + selectSql);
            ResultSet rs = mysqlImpl.get(selectSql);
            UserDTO oneUser = new UserDTO();
            String positionId = "";
           
            String managerId = "";
            while (rs.next()) {
                oneUser = new UserDTO();
                oneUser.setId(rs.getString("id"));
                oneUser.setName(rs.getString("name"));
                oneUser.setUserName(rs.getString("userName"));
                oneUser.setPassword(rs.getString("password"));
                oneUser.setAddress(rs.getString("address"));
                oneUser.setEmail(rs.getString("email"));
                oneUser.setPhoneNumber(rs.getInt("phoneNumber"));
                positionId = rs.getString("positionId");
               
                managerId = rs.getString("mgrId");
                oneUser.setPosition(new PositionDAOImpl().findPosition(positionId));
               
                if (!managerId.equalsIgnoreCase(oneUser.getId()))
                oneUser.setManager(new UserDAOImpl().findUser(managerId));
                oneUser.setConsumeAnnualAllowance(rs.getDouble("consumeAnnualAllowance"));
                oneUser.setConsumeMedicalAllowance(rs.getDouble("consumeMedicalAllowance"));
                items.add(oneUser);
            }
           
        } catch (Exception e) {
            String error = "Error selecting users. Nested Exception is: " + e;
            Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
            throw new DAOSystemException(error);
        } finally {
            try {
                mysqlImpl.close();
            } catch (Exception e) {
            }
        }
        return items;
    }

   

    public void insertUser(UserDTO user) throws DAOSystemException {
    	String insertSql="";
    	 if(user.getPosition()!=null && user.getManager()!=null)
    	 {
    		 	insertSql = "INSERT INTO user VALUES('" 
        				 + user.getId()
                + "', '" + user.getName() 
                + "', '" + user.getUserName()
                + "', '" + user.getPassword()
                + "', '" + user.getAddress()
                + "', '" + user.getEmail()
                + "', '" + user.getPhoneNumber()
                + "', '" + user.getPosition().getId()
                + "', '" + user.getManager().getId()
                + "', '" + user.getConsumeAnnualAllowance()
                + "', '" + user.getConsumeMedicalAllowance()
                + "');";
    		 	System.out.println(insertSql);
    	 } else {
    		 System.out.println("Query not formed!!!!!!!!!!!!!!!!!!");
    	 }
        try {
           
            Logger.getLogger(getClass().getName()).log(Level.INFO,
                    "Executing insert: " + insertSql);
            int res = mysqlImpl.execute(insertSql);
           
        } catch (Exception e) {
            String error = "Error selecting users. Nested Exception is: " + e;
            Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
            throw new DAOSystemException(error);
        } finally {
            try {
                mysqlImpl.close();
            } catch (Exception e) {
            }
        }
    }

    public void updateUser(UserDTO user) throws DAOSystemException {
    	String updateSql = "UPDATE user SET name = '" + user.getName()
        + "', userName = '" + user.getUserName() + "', password = '"
        + user.getPassword() + "', address = '" + user.getAddress()
        + "', email = '" + user.getEmail() + "', phoneNumber = '"
        + user.getPhoneNumber() + "', positionId = '"
        + user.getPosition().getId() + "', mgrId = '"
        + user.getManager().getId() + "',consumeAnnualAllowance= '"
        + user.getConsumeAnnualAllowance() + "',consumeMedicalAllowance= '"
        + user.getConsumeMedicalAllowance()+ "' WHERE id='" + user.getId() + "';";
       
        try {
           
            Logger.getLogger(getClass().getName()).log(Level.INFO,
                    "Executing update: " + updateSql);
            int res = mysqlImpl.execute(updateSql);
           
        } catch (Exception e) {
            String error = "Error selecting users. Nested Exception is: " + e;
            Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
            throw new DAOSystemException(error);
        } finally {
            try {
                mysqlImpl.close();
            } catch (Exception e) {
            }
        }

    }



}